package seismosurfer.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Map;

import seismosurfer.data.constants.QuakeConstants;
import seismosurfer.util.SeismoException;

/**
 * Provides access to min/max values of various columns in the database.
 *
 */
public class MinMaxDAO implements QuakeConstants{    

    protected static final String MAX_DEPTH_QUERY = " SELECT MAX(depth) from quake_mag_catalog ";

    protected static final String MAX_LATITUDE_QUERY = " SELECT "
            + " MAX(q.epicenter.SDO_POINT.Y) from quake_mag_catalog q ";

    protected static final String MAX_LONGITUDE_QUERY = " SELECT "
            + " MAX(q.epicenter.SDO_POINT.X) from quake_mag_catalog q ";

    protected static final String MAX_MAGNITUDE_QUERY = " SELECT MAX(magnitude) "
            + " from mag_view ";

    protected static final String MAX_TIME_QUERY = " SELECT MAX(DATETIME) from quake_mag_catalog ";

    protected static final String MIN_DEPTH_QUERY = " SELECT MIN(depth) from quake_mag_catalog ";

    protected static final String MIN_LATITUDE_QUERY = " SELECT MIN(q.epicenter.SDO_POINT.Y) "
            + " from quake_mag_catalog q ";

    protected static final String MIN_LONGITUDE_QUERY = " SELECT MIN(q.epicenter.SDO_POINT.X) "
            + " from quake_mag_catalog q ";

    protected static final String MIN_MAGNITUDE_QUERY = " SELECT MIN(magnitude) "
            + " from mag_view ";

    protected static final String MIN_TIME_QUERY = " SELECT MIN(DATETIME) from quake_mag_catalog ";

    protected Map mmv = new HashMap();
    
    /**
     * Default empty constructor.
     *
     */
    public MinMaxDAO() {
        
    }
    
    /**
     * Gets a map object with columns-min/max values pairs. The names 
     * of the columns can be found at QuakeConstants interface.
     * 
     * @return a map object with columns-min/max values pairs
     */
    public Map getMinMaxValues() {        

        mmv.put(MAX_TIME, getLongResult(MAX_TIME_QUERY));

        mmv.put(MAX_LATITUDE, getDoubleResult(MAX_LATITUDE_QUERY));
        mmv.put(MAX_LONGITUDE, getDoubleResult(MAX_LONGITUDE_QUERY));
        mmv.put(MAX_DEPTH, getDoubleResult(MAX_DEPTH_QUERY));
        mmv.put(MAX_MAGNITUDE, getDoubleResult(MAX_MAGNITUDE_QUERY));

        mmv.put(MIN_TIME, getLongResult(MIN_TIME_QUERY));

        mmv.put(MIN_LATITUDE, getDoubleResult(MIN_LATITUDE_QUERY));
        mmv.put(MIN_LONGITUDE, getDoubleResult(MIN_LONGITUDE_QUERY));
        mmv.put(MIN_DEPTH, getDoubleResult(MIN_DEPTH_QUERY));
        mmv.put(MIN_MAGNITUDE, getDoubleResult(MIN_MAGNITUDE_QUERY));        

        return mmv;
    }
    
    /**
     * Executes a SQL query, whose result is a long number.
     * 
     * @param  queryName  The SQL query to be executed.
     * @return The result of the SQL query as Long.
     */
    protected Long getLongResult(String queryName) {

        long time = -1;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            stmt = DB.prepare(queryName);

            rs = stmt.executeQuery();

            if (rs.next()) {
                Timestamp datetime = rs.getTimestamp(1);
                time = datetime.getTime();
            }

            return new Long(time);
            
        } catch (SQLException e) {
            throw new SeismoException(e);
        } finally {
            DB.cleanUp(stmt, rs);
        }
    }
    
    /**
     * Executes a SQL query, whose result is a double number.
     * 
     * @param  queryName  The SQL query to be executed.
     * @return The result of the SQL query as Double.
     */
    protected Double getDoubleResult(String queryName) {

        double doubleValue = -1;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {

            stmt = DB.prepare(queryName);
            rs = stmt.executeQuery();

            if (rs.next()) {
                doubleValue = rs.getDouble(1);
            }

            return new Double(doubleValue);

        } catch (SQLException e) {
            throw new SeismoException(e);
        } finally {
            DB.cleanUp(stmt, rs);
        }

    }    

}
